ALTER PROC SP_GET_INFOR_OF_THUE_PHONG_4_EDIT
@ID INT
AS
BEGIN
	SELECT  
		THUE_PHONG.ID,
		NGAY_THUE, 
		ISNULL(TIEN_COC,0) TIEN_COC,
		ISNULL(DON_GIA_PHONG,0) DON_GIA_PHONG, 
		THU_TIEN.ID THU_TIEN_ID, 
		ISNULL(THU_TIEN.CHI_SO_NUOC_SAU,0) CHI_SO_NUOC, 
		ISNULL(THU_TIEN.CHI_SO_DIEN_SAU,0) CHI_SO_DIEN, 
		ISNULL(THU_TIEN.SO_THANG, 0) SO_THANG,
		THU_TIEN.NGAY_KT_KY_TRUOC NGAY_VAO_O, 
		DBO.F_COUNT_COLLECT_HIST(@ID) AS NCOUNT
	FROM 
		THUE_PHONG, 
		(SELECT TOP 1 ID, SO_THUE_PHONG, NGAY_KT_KY_TRUOC, SO_THANG, CHI_SO_NUOC_SAU,  CHI_SO_DIEN_SAU FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID ORDER BY ID ) THU_TIEN
	WHERE 
	THUE_PHONG.ACTIVE = 'A'
	AND THUE_PHONG.ID = @ID
	AND THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG
END

CREATE PROC SP_UPDATE_THUE_PHONG
@SO_THUE_PHONG INT, 
@MA_PHONG INT, 
@TIEN_COC FLOAT, 
@DON_GIA FLOAT, 
@SO_THANG INT,
@CHI_SO_NUOC FLOAT,
@CHI_CHO_DIEN FLOAT, 
@NGAY_VAO_O DATE,
@FLG INT, 
@USR_ID INT
AS
BEGIN
	UPDATE THUE_PHONG 
		SET MA_PHONG = @MA_PHONG , 
			TIEN_COC = @TIEN_COC, 
			DON_GIA_PHONG = @DON_GIA, 
			UPDATE_AT = GETDATE(), 
			UPDATE_BY = @USR_ID
	WHERE ID = @SO_THUE_PHONG
		
	IF @FLG =1 
	BEGIN
		UPDATE THU_TIEN 
		SET 
		CHI_SO_NUOC_SAU = @CHI_SO_NUOC, 
		CHI_SO_DIEN_SAU = @CHI_CHO_DIEN, 
		NGAY_KT_KY_TRUOC = @NGAY_VAO_O, 
		NGAY_KET_THUC_KY_NAY = DATEADD(MONTH,@SO_THANG,@NGAY_VAO_O), 
		SO_THANG = @SO_THANG,
		UPDATE_AT = GETDATE(), 
		UPDATE_BY = @USR_ID
		WHERE 
		ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG
	END
END

ALTER PROC [dbo].[SP_INSERT_THU_TIEN]
@SO_THUE_PHONG INT, 
@NGAY_THU DATE, 
@NGUOI_THU INT, 
@NGAY_KT_KY_TRUOC DATE, 
@NGAY_KET_THUC_KY_NAY DATE, 
@CHI_SO_NUOC_TRUOC FLOAT, 
@CHI_SO_NUOC_SAU FLOAT,
@CHI_SO_DIEN_TRUOC FLOAT, 
@CHI_SO_DIEN_SAU FLOAT,
@DON_GIA_DIEN FLOAT, 
@DON_GIA_NUOC FLOAT, 
@TIEN_PHONG FLOAT,
@TIEN_GUI_XE FLOAT,
@TIEN_PHU_THU FLOAT,
@SO_THANG INT
AS
BEGIN
	INSERT INTO THU_TIEN
	(
		SO_THUE_PHONG,
		NGAY_THU,
		NGUOI_THU,
		NGAY_KT_KY_TRUOC,
		NGAY_KET_THUC_KY_NAY,
		CHI_SO_NUOC_TRUOC,
		CHI_SO_NUOC_SAU,
		CHI_SO_DIEN_TRUOC,
		CHI_SO_DIEN_SAU,
		DON_GIA_DIEN,
		DON_GIA_NUOC,
		TIEN_PHONG,
		TIEN_GUI_XE,
		TIEN_PHU_THU,
		SO_THANG,
		CREATE_BY,
		UPDATE_BY,
		CREATE_AT,
		UPDATE_AT,
		ACTIVE
	)
	VALUES
	(
		@SO_THUE_PHONG , 
		@NGAY_THU , 
		@NGUOI_THU , 
		CASE @SO_THANG 
		WHEN 0 THEN
			NULL
		ELSE
			@NGAY_KT_KY_TRUOC 
		END,
		CASE @SO_THANG 
		WHEN 0 THEN
			NULL
		ELSE
			@NGAY_KET_THUC_KY_NAY 
		END, 
		@CHI_SO_NUOC_TRUOC , 
		@CHI_SO_NUOC_SAU ,
		@CHI_SO_DIEN_TRUOC , 
		@CHI_SO_DIEN_SAU ,
		@DON_GIA_DIEN , 
		@DON_GIA_NUOC , 
		@TIEN_PHONG ,
		@TIEN_GUI_XE ,
		@TIEN_PHU_THU ,
		@SO_THANG,
		@NGUOI_THU,
		@NGUOI_THU,
		GETDATE(), 
		GETDATE(), 
		'A' 
	)	
	
	DELETE THU_TIEN 
	WHERE ACTIVE = 'P' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
	UPDATE CHI_TIEU
	SET 
	ACTIVE = 'C', -- COMPLETE,
	MA_PHIEU_THU =  (SELECT SCOPE_IDENTITY())
	WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @SO_THUE_PHONG;
	
END



CREATE FUNCTION F_GET_MAX_START
(@ID INT)
RETURNS DATE
AS 
BEGIN
	RETURN (SELECT MAX(NGAY_KT_KY_TRUOC) FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END

CREATE FUNCTION F_GET_MAX_END_DATE
(@ID INT)
RETURNS DATE
AS 
BEGIN
	RETURN (SELECT MAX(NGAY_KET_THUC_KY_NAY) FROM THU_TIEN WHERE ACTIVE = 'A' AND SO_THUE_PHONG = @ID)
END

ALTER PROC [dbo].[SP_GET_THUE_PHONG_LIST]
@SEARCH_STRING NVARCHAR(MAX)
AS
BEGIN
	SELECT
		THUE_PHONG.ID, 
		THUE_PHONG.MA_PHONG, 
		KHACH_THUE.HO_VA_TEN,
		KHACH_THUE.HINH_ANH,
		PHONG.SO_PHONG,
		ISNULL(THU_TIEN.NGAY_THU, GETDATE()) NGAY_THU, 
		DBO.F_GET_MAX_END_DATE(THUE_PHONG.ID) NGAY_KET_THUC_KY_NAY
	FROM 
		THUE_PHONG
		JOIN PHONG ON THUE_PHONG.MA_PHONG = PHONG.ID
		LEFT JOIN THU_TIEN 
		ON THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG AND THU_TIEN.ID = (SELECT MAX(ID) FROM THU_TIEN WHERE ACTIVE = 'A' AND THUE_PHONG.ID = THU_TIEN.SO_THUE_PHONG) 
		JOIN KHACH_THUE ON THUE_PHONG.ID = KHACH_THUE.SO_THUE_PHONG		
	WHERE
		THUE_PHONG.ACTIVE = 'A'
		AND KHACH_THUE.LEVEL = 1
		AND THUE_PHONG.NGAY_TRA IS NULL
		AND (@SEARCH_STRING = '' OR @SEARCH_STRING IS NULL OR UPPER(KHACH_THUE.HO_VA_TEN) LIKE '%' + UPPER(@SEARCH_STRING) + '%' OR  CAST(PHONG.SO_PHONG AS NVARCHAR) = @SEARCH_STRING )
		AND KHACH_THUE.ACTIVE = 'A'
	ORDER BY DBO.F_GET_MAX_END_DATE(THUE_PHONG.ID)
END


CREATE PROC SP_DELETE_THU_TIEN
@ID INT, 
@USR_ID INT
AS
BEGIN
	UPDATE THU_TIEN
	SET 
	ACTIVE = 'I',
	UPDATE_AT = GETDATE(), 
	UPDATE_BY = @USR_ID
	WHERE ID = @ID
END